﻿/********************************************************
 * 类名： InitPurDetailsReportDAL.cs
 * 功能： 采购明细报表数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2014-02-26	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using PSS.Entity.ReportManage.PurchasingReport;
using PSS.Utility;

namespace PSS.DAL.ReportManage.PurchasingReport
{
    public class InitPurDetailsReportDAL : DbHelperBase
    {
        public List<InitPurDetailsReportEntity> GetInitPurDetailsReportList(InitPurDetailsReportQcEntity qcmodel)
        {
            var list = new List<InitPurDetailsReportEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT A.InitPurId,A.DocDate,A.DocNumber,A.BusinessCategory,F.VendorCode,F.VendorName,C.GoodsCode,C.GoodsName,C.Specific,E.UnitName,D.StorageName,B.Qty,B.PurchasePrice,B.PurchaseAmount FROM PSS_InitPur AS A  ");
            strSql.Append(" INNER JOIN PSS_InitPurDetail AS B ON A.InitPurId=B.InitPurId ");
            strSql.Append(" INNER JOIN PSS_Goods AS C ON B.GoodsId=C.GoodsId ");
            strSql.Append(" INNER JOIN PSS_Storage AS D ON B.StorageId=D.StorageId ");
            strSql.Append(" INNER JOIN PSS_Unit AS E ON C.UnitId=E.UnitId ");
            strSql.Append(" INNER JOIN PSS_Vendor AS F ON A.VendorId=F.VendorId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
            {
                strSql.Append(" AND A.DocDate >=@SDocDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
            {
                strSql.Append(" AND A.DocDate <=@EDocDate");
            }
            strSql.Append(" ) AS T ");
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            else
            {
                strSql.Append(" ORDER BY T.DocNumber ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InitPurDetailsReportEntity();
                            if (reader["InitPurId"] != DBNull.Value)
                                model.InitPurId = new Guid(reader["InitPurId"].ToString());
                            if (reader["DocDate"] != DBNull.Value)
                                model.DocDate = Convert.ToDateTime(reader["DocDate"].ToString());
                            model.DocNumber = reader["DocNumber"] == obj ? null : Convert.ToString(reader["DocNumber"]);
                            if (reader["BusinessCategory"] != DBNull.Value)
                                model.BusinessCategory = Convert.ToInt32(reader["BusinessCategory"].ToString());
                            model.VendorCode = reader["VendorCode"] == obj ? null : Convert.ToString(reader["VendorCode"]);
                            model.VendorName = reader["VendorName"] == obj ? null : Convert.ToString(reader["VendorName"]);
                            model.GoodsCode = reader["GoodsCode"] == obj ? null : Convert.ToString(reader["GoodsCode"]);
                            model.GoodsName = reader["GoodsName"] == obj ? null : Convert.ToString(reader["GoodsName"]);
                            model.Specific = reader["Specific"] == obj ? null : Convert.ToString(reader["Specific"]);
                            model.UnitName = reader["UnitName"] == obj ? null : Convert.ToString(reader["UnitName"]);
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (model.BusinessCategory == 2)
                            {
                                if (reader["Qty"] != DBNull.Value)
                                    model.Qty = -Convert.ToDecimal(reader["Qty"].ToString());
                                if (reader["PurchaseAmount"] != DBNull.Value)
                                    model.PurchaseAmount = -Convert.ToDecimal(reader["PurchaseAmount"].ToString());
                            }
                            else
                            {
                                if (reader["Qty"] != DBNull.Value)
                                    model.Qty = Convert.ToDecimal(reader["Qty"].ToString());
                                if (reader["PurchaseAmount"] != DBNull.Value)
                                    model.PurchaseAmount = Convert.ToDecimal(reader["PurchaseAmount"].ToString());
                            }
                            if (reader["PurchasePrice"] != DBNull.Value)
                                model.PurchasePrice = Convert.ToDecimal(reader["PurchasePrice"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<ExportInitPurDetailsReportEntity> ExportInitPurDetailsReport(InitPurDetailsReportQcEntity qcmodel)
        {
            var list = new List<ExportInitPurDetailsReportEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT A.DocDate,A.DocNumber,A.BusinessCategory,F.VendorCode,F.VendorName,C.GoodsCode,C.GoodsName,C.Specific,E.UnitName,D.StorageName,B.Qty,B.PurchasePrice,B.PurchaseAmount FROM PSS_InitPur AS A  ");
            strSql.Append(" INNER JOIN PSS_InitPurDetail AS B ON A.InitPurId=B.InitPurId ");
            strSql.Append(" INNER JOIN PSS_Goods AS C ON B.GoodsId=C.GoodsId ");
            strSql.Append(" INNER JOIN PSS_Storage AS D ON B.StorageId=D.StorageId ");
            strSql.Append(" INNER JOIN PSS_Unit AS E ON C.UnitId=E.UnitId ");
            strSql.Append(" INNER JOIN PSS_Vendor AS F ON A.VendorId=F.VendorId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
            {
                strSql.Append(" AND A.DocDate >=@SDocDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
            {
                strSql.Append(" AND A.DocDate <=@EDocDate");
            }
            strSql.Append(" ) AS T ");

            strSql.Append(" ORDER BY T.DocNumber ");

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.DocDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.DocDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EDocDate", DbType.DateTime, Convert.ToDateTime(qcmodel.DocDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ExportInitPurDetailsReportEntity();
                            if (reader["DocDate"] != DBNull.Value)
                                model.DocDate = Convert.ToDateTime(reader["DocDate"].ToString());
                            model.DocNumber = reader["DocNumber"] == obj ? null : Convert.ToString(reader["DocNumber"]);
                            var cate = Convert.ToInt32(reader["BusinessCategory"].ToString());
                            model.BusinessCategory = cate == 1 ? "购货" : "退货";
                            model.VendorCode = reader["VendorCode"] == obj ? null : Convert.ToString(reader["VendorCode"]);
                            model.VendorName = reader["VendorName"] == obj ? null : Convert.ToString(reader["VendorName"]);
                            model.GoodsCode = reader["GoodsCode"] == obj ? null : Convert.ToString(reader["GoodsCode"]);
                            model.GoodsName = reader["GoodsName"] == obj ? null : Convert.ToString(reader["GoodsName"]);
                            model.Specific = reader["Specific"] == obj ? null : Convert.ToString(reader["Specific"]);
                            model.UnitName = reader["UnitName"] == obj ? null : Convert.ToString(reader["UnitName"]);
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (cate == 2)
                            {
                                if (reader["Qty"] != DBNull.Value)
                                    model.Qty = -Convert.ToDecimal(reader["Qty"].ToString());
                                if (reader["PurchaseAmount"] != DBNull.Value)
                                    model.PurchaseAmount = -Convert.ToDecimal(reader["PurchaseAmount"].ToString());
                            }
                            else
                            {
                                if (reader["Qty"] != DBNull.Value)
                                    model.Qty = Convert.ToDecimal(reader["Qty"].ToString());
                                if (reader["PurchaseAmount"] != DBNull.Value)
                                    model.PurchaseAmount = Convert.ToDecimal(reader["PurchaseAmount"].ToString());
                            }
                            if (reader["PurchasePrice"] != DBNull.Value)
                                model.PurchasePrice = Convert.ToDecimal(reader["PurchasePrice"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
